﻿using Model;
using System.Data;

namespace BLL
{
    public class Lc_QueueBLL : BaseBLL
    {
        public Lc_QueueBLL()
        {
        }

        public Lc_QueueBLL(Service service)
        {
            this.Service = service;
        }

        public DataTable GetAllInfo(string Type, object param)
        {
            string sql = string.Format(@"SELECT
    lcq.N_QUEUE_ID,
    lcq.N_PROCESS_ID,
    lcq.N_NODE_ID,
    lcq.N_QUEUE_NUMBER,
    lcq.C_QUEUE_TRANSACTION_ID,
    CASE lcq.N_AUDIT_TYPE
    WHEN 0 THEN '单人办理'
    WHEN 1 THEN '多人审核1人通过即通过'
    WHEN 2 THEN '多人审核全部通过才通过'
    WHEN 3 THEN '指定人员办理'
    WHEN 4 THEN '默认为片区管理员'
    WHEN 5 THEN '指定特定操作产生的人员'
    END as N_AUDIT_TYPE,
    lcq.C_QUEUE_BLOCK,
    lcq.C_SPICAL_TABLE,
    lcq.C_SPICAL_COL,
    lcp.C_PROCESS_NAME,
    lcn.C_NODE_NAME
FROM
    Lc_Queue lcq,
    Lc_Process lcp,
    Lc_Node lcn
WHERE
    lcq.N_PROCESS_ID = lcp.N_PROCESS_ID
    AND lcq.N_NODE_ID = lcn.N_NODE_ID");
            if (Type == "Condition")
            {
                string[] strs = (string[])param;
                if (strs[0] != "0")
                {
                    sql += " AND lcq.N_PROCESS_ID=" + strs[0];
                }
                if (strs[1] != "请选择办理人" && !string.IsNullOrEmpty(strs[1]))
                {
                    sql += " AND lcq.C_QUEUE_TRANSACTION_ID like '%" + strs[1] + "%' ";
                }
                if (strs[2] != "0")
                {
                    sql += " AND lcq.N_NODE_ID=" + strs[2];
                }
                if (strs[3] != "-1")
                {
                    sql += " AND lcq.N_AUDIT_TYPE=" + strs[3];
                }
            }
            else if (Type == "NID")
            {
                string strC = param.ToString();
                string[] str = strC.Split(',');
                if (str[0].ToString() != "-1")
                {
                    sql += " AND lcq.N_PROCESS_ID=" + str[0];
                }
                if (str[1].ToString() != "-1")
                {
                    sql += " AND lcq.N_NODE_ID=" + str[1];
                }
            }
            else if (Type == "ProcessID")
            {
                sql += " AND lcq.N_PROCESS_ID=" + param;
            }
            else
            {
            }
            sql += " ORDER BY lcq.N_QUEUE_NUMBER ASC";
            DataTable dt = Query(sql);
            return dt;
        }

        public Lc_Queue GetInfoById(string id)
        {
            string sql = string.Format(@"select * from lc_queue t where t.n_queue_id =" + id);

            DataTable dt = Query(sql, "", "GetInfoById", "查询队列", "");
            Lc_Queue model = new Lc_Queue();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.N_QUEUE_ID = decimal.Parse(dt.Rows[0]["N_QUEUE_ID"].ToString());
                model.N_PROCESS_ID = decimal.Parse(dt.Rows[0]["N_PROCESS_ID"].ToString());
                model.N_NODE_ID = decimal.Parse(dt.Rows[0]["N_NODE_ID"].ToString());
                model.N_QUEUE_NUMBER = decimal.Parse(dt.Rows[0]["N_QUEUE_NUMBER"].ToString());
                model.C_QUEUE_TRANSACTION_ID = dt.Rows[0]["C_QUEUE_TRANSACTION_ID"].ToString();
                model.N_AUDIT_TYPE = decimal.Parse(dt.Rows[0]["N_AUDIT_TYPE"].ToString());
                model.C_QUEUE_BLOCK = dt.Rows[0]["C_QUEUE_BLOCK"].ToString();
                model.C_SPICAL_TABLE = dt.Rows[0]["C_SPICAL_TABLE"].ToString();
                model.C_SPICAL_COL = dt.Rows[0]["C_SPICAL_COL"].ToString();

                model.N_TRAN_TYPE = decimal.Parse(dt.Rows[0]["N_TRAN_TYPE"].ToString());
                model.C_POSTS = dt.Rows[0]["C_POSTS"] == null ? "" : dt.Rows[0]["C_POSTS"].ToString();

                model.N_COMPANYID = decimal.Parse(dt.Rows[0]["N_COMPANYID"].ToString());
                model.N_CANCEL = int.Parse(dt.Rows[0]["N_CANCEL"].ToString());
                model.N_CHANGE = int.Parse(dt.Rows[0]["N_CHANGE"].ToString());
            }
            return model;
        }

        /// <summary>
        /// 通过ID查询队列
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Lc_Queue Find(decimal id)
        {
            return GetInfoById(id.ToString());
        }

        public int INSERT(Lc_Queue model, string operName)
        {
            model.N_QUEUE_ID = GetSequence("N_QUEUE_ID");
            string sql = string.Format(@"insert into lc_queue
  (n_queue_id,
   n_process_id,
   n_node_id,
   n_queue_number,
   c_queue_transaction_id,
   n_audit_type,
   c_queue_block,
   c_spical_table,
   c_spical_col,
   n_tran_type,
   c_posts,
   n_companyid,
   n_cancel,
   n_change)
values
  ({13}, {0},{1}, {2}, '{3}', {4}, '{5}','{6}', '{7}',{8}, '{9}',{10},{11},
   {12})", model.N_PROCESS_ID, model.N_NODE_ID, model.N_QUEUE_NUMBER, model.C_QUEUE_TRANSACTION_ID,
         model.N_AUDIT_TYPE, model.C_QUEUE_BLOCK, model.C_SPICAL_TABLE, model.C_SPICAL_COL, model.N_TRAN_TYPE,
         model.C_POSTS, model.N_COMPANYID, model.N_CANCEL, model.N_CHANGE, model.N_QUEUE_ID);

            int count = NoQuery(sql, "新增队列", "INSERT", operName);
            return count;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="operName"></param>
        /// <returns></returns>
        public bool Insert(Lc_Queue entity, string operName)
        {
            return INSERT(entity, operName) > 0 ? true : false;
        }

        public int UPDATE(Lc_Queue model, string operName)
        {
            string sql = @"update lc_queue
   set N_PROCESS_ID            =" + model.N_PROCESS_ID + @",
       n_node_id              = " + model.N_NODE_ID + @",
       n_queue_number         = " + model.N_QUEUE_NUMBER + @",
       c_queue_transaction_id = '" + model.C_QUEUE_TRANSACTION_ID + @"',
       n_audit_type           = " + model.N_AUDIT_TYPE + @",
       c_queue_block          = '" + model.C_QUEUE_BLOCK + @"',
       c_spical_table         = '" + model.C_SPICAL_TABLE + @"',
       c_spical_col           = '" + model.C_SPICAL_COL + @"',
       n_tran_type            = " + model.N_TRAN_TYPE + @",
       c_posts                = '" + model.C_POSTS + @"',
       n_companyid            = " + model.N_COMPANYID + @",
       n_cancel               = " + model.N_CANCEL + @",
       n_change               = " + model.N_CHANGE + @"
 where n_queue_id=" + model.N_QUEUE_ID;
            //   string sql = string.Format(@"UPDATE THKQS.LC_QUEUE SET N_PROCESS_ID='{0}', N_NODE_ID='{1}', C_QUEUE_TRANSACTION_ID='{2}', C_QUEUE_BLOCK='{3}', N_AUDIT_TYPE='{4}', N_QUEUE_NUMBER='{5}', C_SPICAL_TABLE='{6}', C_SPICAL_COL='{7}' WHERE N_QUEUE_ID='{8}'", model.N_PROCESS_ID, model.N_NODE_ID, model.C_QUEUE_TRANSACTION_ID, model.C_QUEUE_BLOCK, model.N_AUDIT_TYPE, model.N_QUEUE_NUMBER, model.C_SPICAL_TABLE, model.C_SPICAL_COL, model.N_QUEUE_ID);
            int count = NoQuery(sql, operName);
            return count;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="operName"></param>
        /// <returns></returns>
        public bool Update(Lc_Queue entity, string operName)
        {
            return UPDATE(entity, operName) > 0 ? true : false;
        }

        public int DELETE(string ids, string operName)
        {
            string sql = string.Format(@"DELETE FROM THKQS.LC_QUEUE  WHERE N_QUEUE_ID IN(" + ids + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        public Lc_Queue GetNextQueue(decimal proId, string operName)
        {
            string sql = string.Format("SELECT N_QUEUE_ID,N_PROCESS_ID,N_NODE_ID,N_QUEUE_NUMBER,C_QUEUE_TRANSACTION_ID,N_AUDIT_TYPE,C_QUEUE_BLOCK,C_SPICAL_TABLE,C_SPICAL_COL,N_TRAN_TYPE,C_POSTS FROM THKQS.LC_QUEUE WHERE N_PROCESS_ID = {0} AND N_QUEUE_NUMBER = 1", proId);

            DataTable dt = Query(sql);
            Lc_Queue model = new Lc_Queue();

            foreach (DataRow dr in dt.Rows)
            {
                model.N_QUEUE_ID = decimal.Parse(dr["N_QUEUE_ID"].ToString());
                model.N_PROCESS_ID = decimal.Parse(dr["N_PROCESS_ID"].ToString());
                model.N_NODE_ID = decimal.Parse(dr["N_NODE_ID"].ToString());
                model.N_QUEUE_NUMBER = decimal.Parse(dr["N_QUEUE_NUMBER"].ToString());
                model.C_QUEUE_TRANSACTION_ID = dr["C_QUEUE_TRANSACTION_ID"].ToString();
                model.N_AUDIT_TYPE = decimal.Parse(dr["N_AUDIT_TYPE"].ToString());
                model.C_QUEUE_BLOCK = dr["C_QUEUE_BLOCK"].ToString();
                model.C_SPICAL_TABLE = dr["C_SPICAL_TABLE"].ToString();
                model.C_SPICAL_COL = dr["C_SPICAL_COL"].ToString();

                //model.N_CANCEL = int.Parse(dr["N_CANCEL"].ToString());
                //model.N_CHANGE = int.Parse(dr["N_CHANGE"].ToString());
            }

            return model;
        }

        public string GetQueueByQId(decimal queueId, decimal processId, string operName)
        {
            string sql = string.Format(@"SELECT
    N_QUEUE_ID,
    N_PROCESS_ID,
    N_NODE_ID,
    N_QUEUE_NUMBER,
    C_QUEUE_TRANSACTION_ID,
    N_AUDIT_TYPE,
    C_QUEUE_BLOCK,
    C_SPICAL_TABLE,
    C_SPICAL_COL
FROM
    THKQS.Lc_Queue t
WHERE
    t.N_QUEUE_ID=" + queueId + @"
    AND t.N_PROCESS_ID=" + processId + "");
            DataTable dt = Query(sql);
            string num = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    num = dr["N_QUEUE_NUMBER"].ToString();
                }
            }
            return num;
        }

        public string GetPreNum(decimal processId, decimal num, string operName)
        {
            string preNum = "";
            string sql = string.Format(@"SELECT
    N_QUEUE_ID,
    N_PROCESS_ID,
    N_NODE_ID,
    N_QUEUE_NUMBER,
    C_QUEUE_TRANSACTION_ID,
    N_AUDIT_TYPE,
    C_QUEUE_BLOCK,
    C_SPICAL_TABLE,
    C_SPICAL_COL
FROM
    Lc_Queue q
WHERE
    q.N_PROCESS_ID=" + processId + @"
    AND q.N_QUEUE_NUMBER=" + num + @"-1");
            DataTable dt = Query(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    preNum = dr["N_QUEUE_ID"].ToString();
                }
            }
            return preNum;
        }

        public int GetRecordCount(string whereStr, string operName)
        {
            string sql = string.Format(@"SELECT count(1)
FROM
    Lc_Queue lcq,
    Lc_Process lcp,
    Lc_Node lcn
WHERE
    lcq.N_PROCESS_ID = lcp.N_PROCESS_ID
    AND lcq.N_NODE_ID = lcn.N_NODE_ID " + whereStr);

            int count = GetCount(sql);

            return count;
        }

        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
    lcq.N_QUEUE_ID,
    lcq.N_QUEUE_NUMBER,
    lcq.C_QUEUE_TRANSACTION_ID,
    lcq.N_PROCESS_ID,
    lcp.C_PROCESS_NAME,
    lcq.N_TRAN_TYPE,
    lcq.C_POSTS,
    CASE lcq.N_AUDIT_TYPE
    WHEN 0 THEN '单人审核'
    WHEN 1 THEN '多人审核一人通过即通过'
    WHEN 2 THEN '多人审核全部通过才通过'
    WHEN 3 THEN '指定人员办理'
    WHEN 4 THEN '默认为片区管理员'
    WHEN 5 THEN '指定特定操作产生的人员'
    END as N_AUDIT_TYPE,
    lcq.C_QUEUE_BLOCK,
    lcq.C_SPICAL_TABLE,
    lcq.C_SPICAL_COL,
    lcq.N_NODE_ID,
    lcn.C_NODE_NAME
FROM
    Lc_Queue lcq,
    Lc_Process lcp,
    Lc_Node lcn
WHERE
    lcq.N_PROCESS_ID = lcp.N_PROCESS_ID
    AND lcq.N_NODE_ID = lcn.N_NODE_ID ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");// DbHelperSQL.Query(sql);
        }

        public string getContionState(string id)
        {
            string sql = @"SELECT c.N_CONDITION_STATE FROM Lc_Condition c WHERE c.N_CONDITION_STATE!=0 AND c.N_QUEUE_ID_LEFT=" + id + " ORDER BY c.N_CONDITION_STATE DESC";
            DataTable dt = Query(sql);
            return dt.Rows[0][0].ToString();
        }

        public string GetContionStateRight(string id)
        {
            string sql = @"SELECT c.N_CONDITION_STATE FROM Lc_Condition c WHERE c.N_CONDITION_STATE!=0 AND c.N_QUEUE_ID_RIGHT=" + id + " ORDER BY c.N_CONDITION_STATE DESC";
            DataTable dt = Query(sql);
            return dt.Rows[0][0].ToString();
        }

        public string getQueueNumber(string id)
        {
            string sql = @"SELECT lcq.N_QUEUE_NUMBER FROM Lc_Queue lcq WHERE lcq.N_QUEUE_ID=" + id;
            DataTable dt = Query(sql);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return "";
            }
        }

        public DataTable getcon(string processId)
        {
            string sql = @" SELECT * FROM Lc_Condition c WHERE c.N_PROCESS_ID=" + processId + " AND c.N_CONDITION_STATE!=0";
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable getconleft(string processId)
        {
            string sql = @"SELECT DISTINCT(N_QUEUE_ID_LEFT) FROM Lc_Condition c WHERE c.N_CONDITION_STATE!=0 AND c.N_PROCESS_ID= " + processId;

            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable getconright(string processId)
        {
            string sql = @"SELECT DISTINCT(c.N_QUEUE_ID_RIGHT) FROM Lc_Condition c WHERE c.N_PROCESS_ID=" + processId + " AND c.N_QUEUE_ID_RIGHT NOT IN (SELECT DISTINCT(N_QUEUE_ID_LEFT) FROM THKQS.Lc_Condition WHERE N_PROCESS_ID=" + processId + " AND N_CONDITION_STATE!=0) AND c.N_CONDITION_STATE!=0";
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable getqueuer(string queueId, string processId)
        {
            string sql = @"SELECT * FROM Lc_Queue q WHERE q.N_QUEUE_ID IN(SELECT c.N_QUEUE_ID_RIGHT FROM THKQS.Lc_Condition c WHERE c.N_QUEUE_ID_LEFT=" + queueId + " AND c.N_CONDITION_STATE!=0)";
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable getqueue(string queueId, string processId)
        {
            string sql = @"SELECT * FROM Lc_Queue q WHERE q.N_QUEUE_ID=" + queueId;
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable getnode(string nodeId)
        {
            string sql = @"SELECT * FROM Lc_Node n WHERE n.N_NODE_ID=" + nodeId;
            DataTable dt = Query(sql);
            return dt;
        }

        /// <summary>
        /// 查询特定业务的流转队列上的节点顺序(除去归档节点)
        /// </summary>
        /// <param name="processId">流程编号</param>
        /// <param name="win"></param>
        /// <param name="logonName"></param>
        /// <returns></returns>
        public DataTable processQueue(string thisNmu, decimal bid)
        {
            string sql = @"select DISTINCT q.n_queue_id, n.c_node_name,q.n_queue_number
  from lc_queue q, lc_node n, lc_transaction t
 where q.n_node_id = n.n_node_id
   and q.n_queue_id = t.n_queue_id
   and t.n_business_id =" + bid + @"
   and q.n_queue_number <" + thisNmu + @"
   AND q.N_QUEUE_ID != 215
 order by q.n_queue_number";

            DataTable dt = Query(sql);

            if (dt != null && dt.Rows.Count > 0)
            {
                // dt.Rows.RemoveAt(0);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i]["c_node_name"].ToString().Contains("归档"))
                    {
                        dt.Rows.RemoveAt(i);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 根据流程id，查询当前公司所走第一步队列
        /// </summary>
        /// <param name="proid"></param>
        /// <param name="num"></param>
        /// <param name="comid"></param>
        /// <returns></returns>
        public decimal QuByNumCom(decimal proid, decimal comid)
        {
            decimal quid = 0;
            string sql = @"select t.*, t.rowid from lc_queue t where t.n_process_id = " + proid + @" and t.n_queue_number=1 and t.n_companyid=" + comid;
            DataTable dt = Query(sql);

            if (dt != null && dt.Rows.Count > 0)
            {
                quid = decimal.Parse(dt.Rows[0]["n_queue_id"].ToString());
            }
            else
            {
                //当前公司没有 属于本公司的流程，走通用流程
                string sql2 = @"select t.*, t.rowid from lc_queue t where t.n_process_id = " + proid + @" and t.n_queue_number=1 and t.n_companyid=0";
                DataTable dt2 = Query(sql2);
                if (dt2 != null && dt2.Rows.Count > 0)
                {
                    quid = decimal.Parse(dt2.Rows[0]["n_queue_id"].ToString());
                }
            }

            return quid;
        }
    }
}